from threading import Thread
from PyQt5 import QtCore, QtWidgets
from PyQt5.QtGui import QStandardItem, QStandardItemModel
from openpyxl import Workbook
from src.qobj.chginfowgt import chginfowgt
from src.qobj.chgpasswgt import chgpasswgt
from src.qobj.waitwgt import waitwgt
from res.ui.main_window import Ui_MainWindow
from src.util.entity import *
from src.util.mydb2 import mysession
from sqlalchemy.sql import func


class mainwnd(QtWidgets.QMainWindow):
    query_ended = QtCore.pyqtSignal()
    hide_me = QtCore.pyqtSignal()
    show_me = QtCore.pyqtSignal()
    msg_info = QtCore.pyqtSignal(str, str)

    def __init__(self, tno:str) -> None:
        super().__init__()
        ui = Ui_MainWindow()
        ui.setupUi(self)
        ui.tabWidget.currentChanged.connect(self.tab_change)
        self.query_ended.connect(self.switcher)
        self.ui = ui

        self.data = []
        self.on_button = False
        self.tablemap = {
            0: (学生, ui.tabvw_student, self.select_student_by_clsid),
            1: (课程, ui.tabvw_course, self.select_all),
            2: (选修, ui.tabvw_score, self.select_score_by_clsid),
            3: (评价, ui.tabvw_rating, self.select_rating_by_clsid),
            4: (班会, ui.tabvw_meeting, self.select_meeting_by_clsid),
            5: (班干, ui.tabvw_leader, self.select_leader_by_clsid),
            6: (班主任, None, self.select_headteacher_by_tno),
        }

        self.wait = waitwgt("正在查询")
        self.hide_me.connect(self.wait.hide)
        self.show_me.connect(self.wait.show)
        self.msg_info.connect(self.msgbox)
        self.tno = tno

        # 绑定插入删除查询事件
        btns = [ui.btn_ins_s, ui.btn_ins_c, ui.btn_ins_sc,
                ui.btn_ins_r, ui.btn_ins_m, ui.btn_ins_l]
        [btn.clicked.connect(self.insert_line) for btn in btns]
        btns = [ui.btn_del_s, ui.btn_del_c, ui.btn_del_sc,
                ui.btn_del_r, ui.btn_del_m, ui.btn_del_l]
        [btn.clicked.connect(self.delete_line) for btn in btns]
        ui.btn_qry_sscore.clicked.connect(self.qry_sscore)
        ui.btn_qry_cscore.clicked.connect(self.qry_cscore)
        ui.btn_qry_r.clicked.connect(self.qry_rating)

        # 绑定提交和回滚事件
        btns = [ui.btn_cm_1, ui.btn_cm_2, ui.btn_cm_3,
                ui.btn_cm_4, ui.btn_cm_5, ui.btn_cm_6]
        [btn.clicked.connect(mysession.commit) for btn in btns]
        def rollback():
            mysession.rollback()
            self.refresh()
        btns = [ui.btn_rb_1, ui.btn_rb_2, ui.btn_rb_3,
                ui.btn_rb_4, ui.btn_rb_5, ui.btn_rb_6]
        [btn.clicked.connect(rollback) for btn in btns]

        # 绑定导出报表事件
        btns = [ui.btn_exp_1, ui.btn_exp_2, ui.btn_exp_3,
                ui.btn_exp_4, ui.btn_exp_5, ui.btn_exp_6]
        [btn.clicked.connect(self.export_xlsx) for btn in btns]

        # 第一次刷新查询班主任数据
        thr = self.refresh()
        thr.join()
        self.event_headteacher()

        # 绑定按钮
        self.chginfo = chginfowgt(self)
        self.chgpass = chgpasswgt(self)
        ui.btn_upd_t.clicked.connect(self.chginfo.show)
        ui.btn_upd_cls.clicked.connect(self.chginfo.show)
        ui.btn_upd_pass.clicked.connect(self.chgpass.show)
        ui.btn_stat_sc.clicked.connect(self.stat_sc)

    @property
    def tab_idx(self) -> int:
        return self.ui.tabWidget.currentIndex()
    @property
    def cur_type(self):
        return self.tablemap[self.tab_idx][0]
    @property
    def cur_tabvw(self):
        return self.tablemap[self.tab_idx][1]
    @property
    def cur_qry(self):
        return self.tablemap[self.tab_idx][2]

    def closeEvent(self, event):
        """重载退出事件"""
        reply = QtWidgets.QMessageBox.question(self, "提示", "确定退出吗？")
        # 判断返回值，如果点击的是Yes按钮，我们就关闭组件和应用，否则就忽略关闭事件
        if reply == QtWidgets.QMessageBox.Yes:
            mysession.commit()
            event.accept()
        else:
            event.ignore()

    def tab_change(self):
        """改变标签的时候触发"""
        # 如果是按钮就调用处理函数
        if self.on_button:
            self.on_button = False
            return
        self.refresh()

    def refresh(self) -> Thread:
        """刷新一下重新填入数据"""
        # 提交当前更改
        mysession.commit()
        thr = Thread(target=self.thread_query, args=(self.cur_qry, ))
        thr.start()
        return thr

    def qry_sscore(self):
        """按下查询学生成绩"""
        mysession.commit()
        tabvw:QtWidgets.QTableView = self.cur_tabvw
        rows = set([t.row() for t in tabvw.selectedIndexes()])
        if len(rows) == 0: return
        Thread(target=self.thread_query, args=(self.select_score_by_sid,
            self.data[min(rows)].id)).start()
        self.on_button = True
        self.ui.tabWidget.setCurrentIndex(2)

    def qry_cscore(self):
        """按下查询课程成绩"""
        mysession.commit()
        tabvw:QtWidgets.QTableView = self.cur_tabvw
        rows = set([t.row() for t in tabvw.selectedIndexes()])
        if len(rows) == 0: return
        Thread(target=self.thread_query, args=(self.select_score_by_cid_clsid,
            self.data[min(rows)].id)).start()
        self.on_button = True
        self.ui.tabWidget.setCurrentIndex(2)

    def qry_rating(self):
        """按下查询学生评价"""
        mysession.commit()
        tabvw:QtWidgets.QTableView = self.cur_tabvw
        rows = set([t.row() for t in tabvw.selectedIndexes()])
        if len(rows) == 0: return
        Thread(target=self.thread_query, args=(self.select_rating_by_sid,
            self.data[min(rows)].id)).start()
        self.on_button = True
        self.ui.tabWidget.setCurrentIndex(3)

    def stat_sc(self):
        """按下统计成绩"""
        lst = [float(i.成绩) for i in self.data]
        self.msg_info.emit("成绩统计", f"计数：{len(lst)}\n"
            f"平均分：{sum(lst) / len(lst)}\n总分：{sum(lst)}\n")

    def export_xlsx(self) -> None:
        """导出表格"""
        path = QtWidgets.QFileDialog.getSaveFileName(
            caption=f"将“{self.cur_type.__name__}”保存到...",
            filter="Excel 表格 (*.xlsx)",
            directory=f"{self.cur_type.__name__}")
        print(f"保存到: {path}")
        if path[0] == "": return
        wb = Workbook()
        ws = wb.active
        # 第一行是标题
        fields = self.cur_type.field
        ws.append(fields)
        for obj in self.data:
            ws.append([getattr(obj, f) for f in fields])
        wb.save(path[0])

    def msgbox(self, title:str, msg:str) -> None:
        """展示提示框"""
        QtWidgets.QMessageBox.information(self, title, msg)

    def thread_query(self, fn_query, *args):
        """子线程查询"""
        self.show_me.emit()
        try:
            fn_query(*args)
        except Exception as e:
            print(e)
            print("连接失败")
            self.msg_info.emit("连接失败", "服务器连接失败，请稍后再试")
        self.query_ended.emit()
        self.hide_me.emit()

    def select_all(self):
        """查出所有数据"""
        self.data = mysession.query(self.cur_type).all()

    def select_student_by_clsid(self):
        """根据班级id查学生"""
        self.data = mysession.query(学生).\
            filter(学生.班级id == self.tinfo.班级id).all()

    def select_score_by_sid(self, sid):
        """根据学生id查成绩"""
        self.data = mysession.query(选修).\
            filter(选修.学生id == sid).all()

    def select_score_by_cid_clsid(self, cid):
        """根据课程id和班级id查成绩"""
        self.data = mysession.query(选修).join(学生).\
            filter(选修.课程id == cid, 学生.班级id == self.tinfo.班级id).all()

    def select_score_by_clsid(self):
        """根据班级id查成绩"""
        self.data = mysession.query(选修).join(学生).\
            filter(学生.班级id == self.tinfo.班级id).all()

    def select_score_avg(self):
        """查询平均成绩总成绩计数"""
        self.ԁаԏа = mysession.query(
            func.avg(选修.成绩),
            func.sum(选修.成绩),
            func.count(选修.成绩))

    def select_rating_by_clsid(self):
        """根据班级id查评价"""
        self.data = mysession.query(评价).join(学生).\
            filter(学生.班级id == self.tinfo.班级id).all()

    def select_rating_by_sid(self, sid):
        """根据学生id查评价"""
        self.data = mysession.query(评价).\
            filter(评价.学生id == sid).all()

    def select_meeting_by_clsid(self):
        """根据班级id查班会"""
        self.data = mysession.query(班会).\
            filter(班会.班级id == self.tinfo.班级id).all()

    def select_leader_by_clsid(self):
        """根据班级id查班干"""
        self.data = mysession.query(班干).\
            filter(班干.班级id == self.tinfo.班级id).all()

    def select_headteacher_by_tno(self):
        """根据教师号查信息"""
        self.data = mysession.query(班主任).\
            filter(班主任.教师号 == self.tno).all()

    def switcher(self):
        """切换标签栏的时候触发"""
        if self.tab_idx != 6:
            self.fill_table()
        else:
            self.event_headteacher()

    def fill_table(self):
        """数据填充表格"""
        title:tuple = self.cur_type.field
        print(title)
        data = self.data
        row, col = len(data), len(title)
        model = QStandardItemModel(row, col)
        model.setHorizontalHeaderLabels(title)
        for i in range(row):
            lst = [str(getattr(data[i], name)) for name in title]
            for j in range(col):
                model.setItem(i, j, QStandardItem(lst[j]))
        self.cur_tabvw.setModel(model)
        model.dataChanged.connect(self.data_change)

    def event_headteacher(self):
        self.tinfo:班主任 = self.data[0] if len(self.data) else 班主任()
        self.ui.label.setText(f"{self.tinfo.姓名}老师，欢迎！")

    def insert_line(self):
        """插入一行数据"""
        obj = self.cur_type()
        if type(obj) == 学生: obj.班级id = self.tinfo.班级id
        if type(obj) == 评价: obj.班主任id = self.tinfo.id
        if type(obj) == 班会: obj.班级id = self.tinfo.班级id
        if type(obj) == 班干: obj.班级id = self.tinfo.班级id
        self.data.append(obj)
        mysession.add(obj)
        model:QStandardItemModel = self.cur_tabvw.model()
        items = [QStandardItem(getattr(obj, f)) for f in getattr(obj, "field")]
        model.appendRow(items)

    def data_change(self, left_top_idx, *_):
        """改变数据的时候触发"""
        r, c = left_top_idx.row(), left_top_idx.column()
        print(left_top_idx.data(), r, c)
        name = self.data[r].field[c]
        setattr(self.data[r], name, left_top_idx.data())

    def delete_line(self):
        """删除一行数据"""
        tabvw:QtWidgets.QTableView = self.cur_tabvw
        print(tabvw.selectedIndexes())
        rows = set([t.row() for t in tabvw.selectedIndexes()])
        rows = sorted(rows, reverse=True)
        ids = [self.data[i].id for i in rows]
        if len(rows) == 0: return
        reply = QtWidgets.QMessageBox.question(self, "提示",
            f"选中了{len(rows)}行，确定删除吗？")
        if reply == QtWidgets.QMessageBox.No: return
        q = mysession.query(self.cur_type).\
            filter(self.cur_type.id.in_(ids)).delete()
        for i in rows: tabvw.model().removeRow(i)
